package nl.ViewerServer.Database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;

import nl.ViewerServer.Database.Objects.ParameterValue;
import nl.ViewerServer.Exceptions.ParameterException;
import nl.ViewerServer.ServerManager.DataManager.DataManager;
import nl.ViewerServer.ServerManager.DataManager.DataObjects.DataObject;
import nl.ViewerServer.ServerManager.DataManager.Objects.JavaPythonUtil;
import nl.ViewerServer.ServerManager.DataManager.Objects.Lookmark;
import nl.ViewerServer.ServerManager.DataManager.Objects.LookmarkInfo;
import nl.ViewerServer.ServerManager.DataManager.Objects.Parameter;
import nl.ViewerServer.SessionManager.Objects.Job;
import nl.ViewerServer.SessionManager.Objects.Session;

public class DataManagerDatabaseConnection extends DatabaseConnection {
   protected PreparedStatement createLookmark;
   protected PreparedStatement addLookmarkParameterValue;
   protected PreparedStatement addDataObject;
   protected PreparedStatement getDataObject;
   protected PreparedStatement getLookmarkParameterValues;
   protected PreparedStatement getUserLookmarkParameterValues;
   protected PreparedStatement findLookmarkForJob;
   protected PreparedStatement getDataForLookmark;
   protected PreparedStatement updateLookmark;
   protected PreparedStatement getLookmarksForUserAndDataset;
   protected PreparedStatement getLookmark;

   public DataManagerDatabaseConnection() throws ClassNotFoundException, SQLException {
      super();
   }

   protected void prepareStatements() throws SQLException {
      createLookmark = conn.prepareStatement(
            "INSERT INTO `lookmark` (`isUserDefined`, `jobId`, `userId`, `modalityId`, `visualizationId`, `description`, `parameterFingerprint`) VALUES(?, ?, ?, ?, ?, ?, ?)",
            PreparedStatement.RETURN_GENERATED_KEYS );
      addLookmarkParameterValue = conn.prepareStatement( "INSERT INTO `lookmark_parameterValue` (`lookmarkId`, `parameterId`, `parameterValue`) VALUES(?, ?, ?)" );
      addDataObject = conn.prepareStatement(
            "INSERT INTO `lookmark_dataObject` (`lookmarkId`, `location`, `outputDataType`, `resolutionCount`) VALUES(?, ?, ?, ?)",
            PreparedStatement.RETURN_GENERATED_KEYS );
      getLookmark = conn.prepareStatement( "SELECT * from `lookmark` where `lookmarkId` = ?" );
      getDataObject = conn.prepareStatement( "SELECT * FROM `lookmark_dataObject` WHERE `dataObjectId` = ?" );
      getLookmarkParameterValues = conn.prepareStatement( "SELECT p.`parameterName`, pv.`parameterValue` FROM `lookmark_parameterValue` pv, `parameter` p WHERE pv.`lookmarkId` = ? AND p.`parameterId` = pv.`parameterId`" );
      getUserLookmarkParameterValues = conn.prepareStatement( "SELECT p.`parameterName`, pv.`parameterValue` FROM `lookmark_parameterValue` pv, `parameter` p WHERE pv.`lookmarkId` = ? AND p.`parameterId` = pv.`parameterId` AND (p.`isUserModifiable` = 1 OR p.`isUserViewable` = 1)" );

      findLookmarkForJob = conn.prepareStatement( "SELECT `lookmarkId`, `userId` FROM `lookmark` WHERE `jobId` = ?" );
      getDataForLookmark = conn.prepareStatement( "SELECT * FROM `lookmark_dataObject` WHERE `lookmarkId` = ?" );

      updateLookmark = conn.prepareStatement( "UPDATE `lookmark` SET `isUserDefined` = 1, `isPersonal` = ?, `description` = ? WHERE `lookmarkId` = ?" );
      getLookmarksForUserAndDataset = conn.prepareStatement( "SELECT * from `lookmark` l, `modality` m, `dataset` d, `pipelineVisualization` v WHERE l.`isUserDefined` = 1 AND l.`userId` = ? AND m.`datasetId` = d.`datasetId` AND l.`modalityId` = m.`modalityId` AND d.`datasetId` = ? AND l.`visualizationId` = v.`visualizationId` ORDER BY l.`lookmarkId` DESC " );
   }

   /**
    * This method will create a lookmark in the database from a given job and session. A lookmark object contains all
    * information to restore a certain visualization, including the modality/visualization id's, and all
    * parameters/parametervalues used. Along with the lookmark entry, a parameter fingerprint is stored to be able to
    * easily locate a lookmark for an exact set of parameter(value)s.
    * 
    * @param job
    * @param session
    * @return
    * @throws SQLException
    */
   public synchronized Lookmark createLookmarkFromSession( Job job, Session session ) throws SQLException {
      // get all pipeline parameter values (e.g. all except the session parameters) for the fingerprint
      HashMap<String, Parameter> parameters = session.getAllVisualizationParameters();
      String fingerprint = DataManager.getParameterFingerprint( parameters );

      createLookmark.clearParameters();
      createLookmark.setBoolean( 1, false );
      createLookmark.setLong( 2, job.jobId );
      createLookmark.setInt( 3, session.userId );
      createLookmark.setInt( 4, session.getModality().getModalityId() );
      createLookmark.setInt( 5, session.getVisualization().visualizationId );
      createLookmark.setString( 6, null );
      createLookmark.setString( 7, fingerprint );
      createLookmark.execute();
      ResultSet rs = createLookmark.getGeneratedKeys();
      if( !rs.next() ) {
         throw new SQLException( "PreparedStatement for creating a lookmark did not return an auto-generated Id" );
      }

      int lookmarkId = rs.getInt( 1 );
      rs.close();

      addLookmarkParameterValues( lookmarkId, parameters );

      Lookmark lookmark = new Lookmark( lookmarkId, job.jobId, session.userId, session.getModality(),
            session.getVisualization(), null, new Timestamp( System.currentTimeMillis() ), "" );

      return lookmark;
   }

   /**
    * @see createLookmarkFromSession
    * 
    * @param lookmarkId
    * @param parameters
    * @throws SQLException
    */
   private void addLookmarkParameterValues( int lookmarkId, HashMap<String, Parameter> parameters ) throws SQLException {
      // create a batch for all parameters
      addLookmarkParameterValue.clearBatch();
      for( Parameter p : parameters.values() ) {
         addLookmarkParameterValue.clearParameters();
         addLookmarkParameterValue.setInt( 1, lookmarkId );
         addLookmarkParameterValue.setInt( 2, p.parameterId );
         addLookmarkParameterValue.setString( 3, p.getPythonRepresentation() );
         addLookmarkParameterValue.addBatch();
      }
      addLookmarkParameterValue.executeBatch();
   }

   /**
    * Add a DataObject to the database, and attach it to a lookmark. A DataObject in the database is nothing more than
    * the specification of the location, and the data type.
    * 
    * @param dataObject
    * @return
    * @throws SQLException
    */
   public synchronized int addDataObject( DataObject dataObject ) throws SQLException {
      if( dataObject == null )
         return -1;

      addDataObject.clearParameters();
      addDataObject.setInt( 1, dataObject.getLookmarkId() );
      addDataObject.setString( 2, dataObject.getLocation() );
      addDataObject.setString( 3, dataObject.getDataType() );
      addDataObject.setInt( 4, dataObject.getResolutionParts().length );
      addDataObject.execute();

      ResultSet rs = addDataObject.getGeneratedKeys();
      if( !rs.next() ) {
         throw new SQLException( "PreparedStatement for creating a dataObject did not return an auto-generated Id" );
      }
      dataObject.setDataObjectId( rs.getInt( 1 ) );
      return dataObject.getDataObjectId();
   }

   /**
    * This method will retrieve a dataobject from the database, based on a provided dataObjectId.
    * 
    * @param dataObjectId
    * @return
    * @throws SQLException
    */
   public synchronized DataObject getDataObject( int dataObjectId ) throws SQLException {
      getDataObject.clearParameters();
      getDataObject.setInt( 1, dataObjectId );
      ResultSet rs = getDataObject.executeQuery();

      if( !rs.next() )
         return null;

      DataObject dataObject = DataManager.createDataObject( rs.getString( "outputDataType" ) );
      if( dataObject == null )
         return null;

      dataObject.setDataObjectId( dataObjectId );
      dataObject.setLookmarkId( rs.getInt( "lookmarkId" ) );
      dataObject.setLocation( rs.getString( "location" ) );

      return dataObject;
   }

   // public synchronized int findLookmark( Session session, String fingerprint ) throws SQLException {
   // String query =
   // "SELECT `lookmarkId` FROM `lookmark` WHERE `modalityId` = " + session.getModality().getModalityId()
   // + " AND `visualizationId` = " + session.getVisualization().visualizationId
   // + " AND `parameterFingerprint` = '" + fingerprint + "'";
   // ResultSet rs = conn.createStatement().executeQuery( query );
   // if( !rs.next() )
   // return -1;
   //
   // return rs.getInt( "lookmarkId" );
   // }

   /**
    * This method will retrieve a DataObject from the database, based on a provided lookmarkId.
    * 
    */
   public synchronized DataObject getDataObjectForLookmark( int lookmarkId ) throws SQLException {
      getDataForLookmark.clearParameters();
      getDataForLookmark.setInt( 1, lookmarkId );
      ResultSet rs = getDataForLookmark.executeQuery();
      if( !rs.next() )
         return null;

      DataObject dataObject = DataManager.createDataObject( rs.getString( "dataType" ) );
      if( dataObject == null )
         return null;

      dataObject.setDataObjectId( rs.getInt( "dataObjectId" ) );
      dataObject.setLookmarkId( rs.getInt( "lookmarkId" ) );
      dataObject.setLocation( rs.getString( "location" ) );

      return dataObject;

   }

   /**
    * This method will find a lookmark in the database, given some jobId
    * 
    * @param jobId
    * @return returns an int[] with 2 elements: lookmarkId and userId (userId to be able to verify the user when
    *         retrieving data)
    * @throws SQLException
    */

   // 
   public synchronized int[] findLookmarkForJob( long jobId ) throws SQLException {
      findLookmarkForJob.clearParameters();
      findLookmarkForJob.setLong( 1, jobId );
      ResultSet rs = findLookmarkForJob.executeQuery();
      if( !rs.next() )
         return null;

      return new int[] { rs.getInt( "lookmarkId" ), rs.getInt( "userId" ) };
   }

   /*
    * public synchronized ArrayList<ParameterValue> getParameterValuesForLookmark( int lookmarkId ) throws SQLException {
    * getLookmarkParameterValues.clearParameters(); getLookmarkParameterValues.setInt( 1, lookmarkId );
    * 
    * ResultSet rs = getLookmarkParameterValues.executeQuery(); ArrayList<ParameterValue> pv = new ArrayList<ParameterValue>();
    * while( rs.next() ) {
    * 
    * String pythonObject = rs.getString( "parameterValue" ); try { Object javaObject =
    * Parameter.parsePythonObjectToJavaObject( pythonObject ); // we don't convert to a parameter java object, we take
    * for granted that the data in the table is a correct // python object // following code will break when a parameter
    * has its type or length changed and old lookmark parameter // values are retrieved pv.add( new ParameterValue(
    * rs.getString( "parameterName" ), javaObject ) ); } catch( ParameterException e ) { log.warn( "Could not parse
    * parameter value '" + pythonObject + "' for '" + rs.getString( "parameterName" ) + "' from database (in
    * lookmark_parameterValue table): " + e ); } } return pv; }
    */

   /**
    * This method is used to retrieve all user-adjustable/viewable parameters for a lookmark from the database.
    * Parameter conversion from strings containing Python objects to Java is handled here.
    * 
    */
   public synchronized ArrayList<ParameterValue> getUserParameterValuesForLookmark( int lookmarkId )
         throws SQLException {
      getUserLookmarkParameterValues.clearParameters();
      getUserLookmarkParameterValues.setInt( 1, lookmarkId );

      ResultSet rs = getUserLookmarkParameterValues.executeQuery();
      ArrayList<ParameterValue> pv = new ArrayList<ParameterValue>();
      while( rs.next() ) {

         String pythonObject = rs.getString( "parameterValue" );
         try {
            Object javaObject = JavaPythonUtil.parsePythonObjectToJavaObject( pythonObject );
            // we take for granted that the data in the table is a correct python object
            // following code will break when a parameter has its type or length changed and old lookmark parameter
            // values are retrieved
            pv.add( new ParameterValue( rs.getString( "parameterName" ), javaObject ) );
         }
         catch( ParameterException e ) {
            log.warn( "Could not parse parameter value '" + pythonObject + "' for '" + rs.getString( "parameterName" ) + "' from database, was the parameter type changed after inserting the lookmark? (in lookmark_parameterValue table): " + e );
         }

      }
      return pv;

   }

   /**
    * This method will alter an existing lookmark and make it a userDefined lookmark, with a given description. The
    * isPersonal parameter will specify who will have access to the lookmark. When true only the creator will be able to
    * access the lookmark, when false, all users for all groups the creating user is in will have access (this is not
    * implemented yet).
    * 
    * @param lookmarkId
    * @param isPersonal
    * @param description
    * @return
    * @throws SQLException
    */
   public synchronized void createUserDefinedLookmark( int lookmarkId, boolean isPersonal, String description )
         throws SQLException {

      updateLookmark.clearParameters();
      updateLookmark.setBoolean( 1, isPersonal );
      updateLookmark.setString( 2, description );
      updateLookmark.setInt( 3, lookmarkId );

      updateLookmark.execute();
   }

   public synchronized ArrayList<LookmarkInfo> getLookmarks( int userId, int datasetId ) throws SQLException {
      getLookmarksForUserAndDataset.clearParameters();
      getLookmarksForUserAndDataset.setInt( 1, userId );
      getLookmarksForUserAndDataset.setInt( 2, datasetId );

      ResultSet rs = getLookmarksForUserAndDataset.executeQuery();
      ArrayList<LookmarkInfo> lookmarks = new ArrayList<LookmarkInfo>();

      while( rs.next() ) {
         lookmarks.add( new LookmarkInfo( rs.getInt( "l.lookmarkId" ), rs.getString( "l.description" ),
               rs.getTimestamp( "l.created" ), true, rs.getInt( "v.visualizationId" ), rs.getString( "v.ACL" ),
               rs.getInt( "m.modalityId" ), rs.getString( "m.ACL" ), datasetId, rs.getString( "d.ACL" ) ) );
      }
      return lookmarks;

   }

   /**
    * Fetch userId, modalityId and visualizationId for a given lookmark
    * 
    * @param lookmarkId
    * @return an int array of length 3 containing the three id's
    * @throws SQLException
    */
   public int[] getIdsForLookmark( int lookmarkId ) throws SQLException {
      getLookmark.clearParameters();
      getLookmark.setInt( 1, lookmarkId );

      ResultSet rs = getLookmark.executeQuery();

      if( !rs.next() )
         return null;

      return new int[] { rs.getInt( "userId" ), rs.getInt( "modalityId" ), rs.getInt( "visualizationId" ) };
   }

}
